package util.office;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import util.toolkit.Constant;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * 导出成Excel工具类
 * @author 徐震霖
 * @since 2018-11-21 11:09
 *
 */
public class WriteExcel {

	//private static String path = "\\data\\kmsximages\\";
	
	/**
	 * 删除指定文件夹下所有文件
	 * @param tpath 文件夹完整绝对路径
	 * @return 成功返回true，失败返回false
	 */
	public static boolean delAllFile(String tpath) {
		boolean flag = false;
		String tmpPath = Constant.SERVERPATH + tpath;
		File file = new File(tmpPath);
		if (!file.exists()) {
			return flag;
		}
		if (!file.isDirectory()) {
			return flag;
		}
		String[] tempList = file.list();
		File temp = null;
		for (int i = 0; i < tempList.length; i++) {
			if (tmpPath.endsWith(File.separator)) {
				temp = new File(tmpPath + tempList[i]);
			} else {
				temp = new File(tmpPath + File.separator + tempList[i]);
			}
			if (temp.isFile()) {
				temp.delete();
				flag = true;
			}
			if (temp.isDirectory()) {
				delAllFile(tmpPath + "/" + tempList[i]);// 先删除文件夹里面的文件
				//delFolder(path + "/" + tempList[i]);// 再删除空文件夹
				flag = true;
			}
		}
		return flag;
	}
	
	/**
	 * 删除指定某张图片
	 * @param photopath 图片路径
	 * @return 成功返回true，失败返回false
	 */
	public static boolean deleteAPhoto(String photopath){
		boolean delete_flag = false;  
		String filePath=Constant.SERVERPATH+photopath;
        File file = new File(filePath); 
        if (file.exists() && file.isFile() && file.delete())
            delete_flag = true; 
        else  
            delete_flag = false;  
        
        return delete_flag;  
	}
	
	/** 
     * 设置某些列的值只能输入预制的数据,显示下拉框. 
     * @param sheet 要设置的sheet. 
     * @param textlist 下拉框显示的内容 
     * @param firstRow 开始行 
     * @param endRow 结束行 
     * @param firstCol 开始列 
     * @param endCol 结束列 
     * @return 设置好的sheet. 
     */  
    public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {  
        // 加载下拉列表内容  
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);  
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列  
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);  
        // 数据有效性对象  
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);  
        sheet.addValidationData(data_validation_list);  
        return sheet;  
    }  
  
    /** 
     * 设置单元格上提示 
     * @param sheet 要设置的sheet. 
     * @param promptTitle 标题 
     * @param promptContent 内容 
     * @param firstRow 开始行 
     * @param endRow 结束行 
     * @param firstCol 开始列 
     * @param endCol 结束列 
     * @return 设置好的sheet. 
     */  
    public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow, int endRow, int firstCol, int endCol) {  
        // 构造constraint对象  
        DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("BB1");  
        // 四个参数分别是：起始行、终止行、起始列、终止列  
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);  
        // 数据有效性对象  
        HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);  
        data_validation_view.createPromptBox(promptTitle, promptContent);  
        sheet.addValidationData(data_validation_view);  
        return sheet;  
    }
	
	/**
	 * 下载教师信息模板
	 * @param name 导出的文件名称
	 * @param ja JSONArray对象（角色信息）
	 * @param schoolid 学校id，用于生成文件夹(如：D:\\tomcatupload\\schoolid\\userName\\name.xls)
	 * @param userName 当前操作的用户名
	 * @return
	 */
	public static String teacherexportExcel2003(String name, JSONArray ja, String schoolid, String userName) {
		
		if (schoolid.equals("")) {
			schoolid = "不存在的学校";
		}
		if (userName.equals("")){
			userName = "不存在的用户名";
		}		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		//创建第一个工作簿
		HSSFSheet sheet = workbook.createSheet();
		//设置工作簿的单元格宽度（有问题）
		//sheet.setColumnWidth(1, (int)(15 + 0.71)*256);
		sheet.autoSizeColumn(0);
		//创建第一行表头（原始表头，如：真实姓名等）
		HSSFRow titlerow = sheet.createRow(0);
		//定义一个单元格
		HSSFCell titlecell = null;
		String[] title = { "真实姓名 *", "性别 *", "出生日期 *", "邮箱 *", "手机号码 *", "固定电话", "单位电话", "住址 *", "职务 *", "毕业院校 *", "入职时间 *", "所属角色 *" };
		for (int i = 0; i < title.length; i++) {
			//创建第一行第一列单元格
			titlecell = titlerow.createCell(i);
			//设置表头字体样式
			HSSFFont titlefont = workbook.createFont();
			titlefont.setFontHeightInPoints((short)14);//字体大小
			titlefont.setFontName("宋体");//字体
			//titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			//获取到第一行一行单元格的样式信息
			HSSFCellStyle cellstyle = workbook.createCellStyle();
			//把字体样式设置到单元格样式中
			cellstyle.setFont(titlefont);
			//给单元格设置样式
			titlecell.setCellStyle(cellstyle);
			//给单元格设置值
			titlecell.setCellValue(title[i]);
		}
		
		//设置前两千列职务单元格为下拉框选择的形式
		String[] postlist = { "科任教师", "班主任", "年级组长", "中层干部", "副校长", "校长" };
		sheet = setHSSFValidation(sheet, postlist, 2, 2002, 8, 8);
		
		
		//创描述说明行
		HSSFRow explainrow = sheet.createRow(1);
		CellRangeAddress region = new CellRangeAddress(1, 1, 0, 11);
		sheet.addMergedRegion(region);
		HSSFCell explaincell  = explainrow.createCell(0);
		//给单元格设置值
		explaincell.setCellValue("说明：表头里带星号的为必填项，非必填项请用“无”代替；所属角色代码选择请参考Sheet1的角色代码说明选择。");
		
		String[] roletitle = { "角色编号", "角色名称", "学校编号" };
		HSSFSheet rolesheet = workbook.createSheet();
		HSSFRow rolerow = rolesheet.createRow(0);
		HSSFCell rolecell = null;
		for (int i = 0; i < roletitle.length; i++) {
			rolecell = rolerow.createCell(i);			
			rolecell.setCellValue(roletitle[i]);
		}
		
		String[] rolelist = new String[ja.size()];
		
		for (int i = 0; i < ja.size(); i++) {
			HSSFRow nextrow = rolesheet.createRow(i + 1);
			JSONObject first = ja.getJSONObject(i);
			Iterator<String> iterator = first.keys(); // 得到key集合
			while (iterator.hasNext()) { // 遍历key集合
				String key1 = (String) iterator.next(); // 得到key
				String value = "";
				if(key1.equals("id")){
					value = first.getString(key1);
					HSSFCell Cell1 = nextrow.createCell(0);
					Cell1.setCellValue(value);
					rolelist[i] = value;
				}
				else if(key1.equals("name")){
					value = first.getString(key1);
					HSSFCell Cell1 = nextrow.createCell(1);
					Cell1.setCellValue(value);
				}
				else if(key1.equals("schoolid")){
					value = first.getString(key1);
					HSSFCell Cell1 = nextrow.createCell(2);
					Cell1.setCellValue(value);
				}
			}
		}
		
		//设置前两千列所属角色单元格为下拉框选择的形式
		sheet = setHSSFValidation(sheet, rolelist, 2, 2002, 11, 11);
		
		try {
			String tmpPath = Constant.SERVERPATH + schoolid + "/" + userName + "/" + name + ".xls";
			File file = new File(tmpPath);
			if (!file.exists()) {
				file.getParentFile().mkdirs();// 保证路径存在
			}
			OutputStream outputStream = new FileOutputStream(tmpPath);
			workbook.write(outputStream);
			outputStream.close();
			return Constant.FICTITIOUSPATH + schoolid + "/" + userName + "/" + name + ".xls";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";		
		
	}
	
	/**
	 * 下载学生信息模板
	 * @param name 导出的文件名称
	 * @param ja JSONArray对象（角色信息）
	 * @param schoolid 学校id，用于生成文件夹(如：D:\\tomcatupload\\schoolid\\userName\\name.xls)
	 * @param userName 当前操作的用户名
	 * @return
	 */
	public static String studentexportExcel2003(String name, String schoolid, String userName) {
		
		if (schoolid.equals("")) {
			schoolid = "不存在的学校";
		}
		if (userName.equals("")){
			userName = "不存在的用户名";
		}	
		//创建一个EXCEL
		HSSFWorkbook workbook = new HSSFWorkbook();
		//创建一个工作簿
		HSSFSheet sheet = workbook.createSheet();
		//创建第一行（表头行）
		HSSFRow titlerow = sheet.createRow(0);
		//新建一个单元格
		HSSFCell titlecell = null;
		//表头信息
		String[] title = { "学生姓名  *", "性别  *", "出生日期  *" };
		for (int i = 0; i < title.length; i++) {
			//获取到第一个单元格
			titlecell = titlerow.createCell(i);
			//设置表头字体样式
			HSSFFont titlefont = workbook.createFont();
			titlefont.setFontHeightInPoints((short)14);//字体大小
			titlefont.setFontName("宋体");//字体
			//titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
			//获取到第一行一行单元格的样式信息
			HSSFCellStyle cellstyle = workbook.createCellStyle();
			//把字体样式设置到单元格样式中
			cellstyle.setFont(titlefont);
			//给单元格设置样式
			titlecell.setCellStyle(cellstyle);
			titlecell.setCellValue(title[i]);
		}
		
		//创描述说明行
		HSSFRow explainrow = sheet.createRow(1);
		CellRangeAddress region = new CellRangeAddress(1, 1, 0, 2);
		sheet.addMergedRegion(region);
		HSSFCell explaincell  = explainrow.createCell(0);
		//给单元格设置值
		explaincell.setCellValue("说明：表头里带星号的为必填项！");
		
		try {
			String tmpPath = Constant.SERVERPATH + schoolid + "/" + userName + "/" + name + ".xls";
			File file = new File(tmpPath);
			if (!file.exists()) {
				file.getParentFile().mkdirs();// 保证路径存在
			}
			OutputStream outputStream = new FileOutputStream(tmpPath);
			workbook.write(outputStream);
			outputStream.close();
			return Constant.FICTITIOUSPATH + schoolid + "/" + userName + "/" + name + ".xls";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";		
		
	}
	
	/**
	 * JSON导出Excel(03版)
	 * @param name 导出的文件名称
	 * @param ja JSONArray对象（数据）
	 * @param schoolid 学校id，用于生成文件夹(如：D:\\tomcatupload\\schoolid\\userName\\name.xls)
	 * @param userName 当前操作的用户名
	 */
	public static String exportExcel2003(String name, JSONArray ja, String schoolid, String userName) {
		
		if (schoolid.equals("")) {
			schoolid = "不存在的学校";
		}
		if (userName.equals("")){
			userName = "不存在的用户名";
		}	
		
		String[] title = { "学生账号", "学生姓名", "学生性别", "家长姓名", "家长性别", "手机号码", "关系", "学生班级", "学生年级" };
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null;
		for (int i = 0; i < title.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
		}		
		
		for (int i = 0; i < ja.size(); i++) {
			HSSFRow nextrow = sheet.createRow(i + 1);
			JSONObject first = ja.getJSONObject(i);
			Iterator<String> iterator = first.keys(); // 得到key集合
			while (iterator.hasNext()) { // 遍历key集合
				String key1 = (String) iterator.next(); // 得到key
				String value = "";
				if(key1.equals("stuid")){
					value = first.getString(key1);
					HSSFCell Cell1 = nextrow.createCell(0);
					Cell1.setCellValue(value);
				}
				else if(key1.equals("realName")){
					value = first.getString(key1);
					HSSFCell Cell1 = nextrow.createCell(1);
					Cell1.setCellValue(value);
				}
				else if(key1.equals("sex")){
					value = first.getString(key1);
					HSSFCell Cell1 = nextrow.createCell(2);
					Cell1.setCellValue(value);
				}
				else if(key1.equals("className")){					
					value = first.getString(key1);
					HSSFCell Cell1 = nextrow.createCell(7);
					Cell1.setCellValue(value);
				}
				else if(key1.equals("gradename")){					
					value = first.getString(key1);
					HSSFCell Cell1 = nextrow.createCell(8);
					Cell1.setCellValue(value);
				}
			}
		}
		
		try {
			String tmpPath = Constant.SERVERPATH + schoolid + "/" + userName + "/" + name + ".xls";
			File file = new File(tmpPath);
			if (!file.exists()) {
				file.getParentFile().mkdirs();// 保证路径存在
			}
			OutputStream outputStream = new FileOutputStream(tmpPath);
			workbook.write(outputStream);
			outputStream.close();
			return Constant.FICTITIOUSPATH + schoolid + "/" + userName + "/" + name + ".xls";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";		
	}
	
	/**
	 * JSON导出Excel(07版)
	 * @param name 导出的文件名称
	 * @param ja JSONArray对象（数据）
	 * @param schoolid 学校id，用于生成文件夹(如：D:\\tomcatupload\\schoolid\\userName\\name.xlsx)
	 * @param userName 当前操作的用户名
	 */
	public static String exportExcel2007(String name, JSONArray ja, String schoolid, String userName) {
		
		String[] title = { "学生账号", "学生姓名", "学生性别", "家长姓名", "家长性别", "手机号码", "关系", "学生班级", "学生年级" };
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet(name);
		
		if (schoolid.equals("")) {
			schoolid = "不存在的学校";
		}
		if (userName.equals("")){
			userName = "不存在的用户名";
		}
		
		XSSFRow row = sheet.createRow(0);
		for (int i = 0; i < title.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellValue(title[i]);
		}
		
		for (int i = 0; i < ja.size(); i++) {
			row = sheet.createRow(i + 1);
			JSONObject jaa = ja.getJSONObject(i);
			Iterator<String> iterator1 = jaa.keys();
			while (iterator1.hasNext()) { // 遍历key集合
				String key1 = (String) iterator1.next(); // 得到key
				String value = "";
				if(key1.equals("stuid")){
					value = jaa.getString(key1);
					XSSFCell cell = row.createCell(0);
					cell.setCellValue(value);
				}
				else if(key1.equals("realName")){
					value = jaa.getString(key1);
					XSSFCell cell = row.createCell(1);
					cell.setCellValue(value);
				}
				else if(key1.equals("sex")){
					value = jaa.getString(key1);
					XSSFCell cell = row.createCell(2);
					cell.setCellValue(value);
				}
				else if(key1.equals("className")){					
					value = jaa.getString(key1);
					XSSFCell cell = row.createCell(7);
					cell.setCellValue(value);
				}
				else if(key1.equals("gradename")){					
					value = jaa.getString(key1);
					XSSFCell cell = row.createCell(8);
					cell.setCellValue(value);
				}
			}
		}
		try {
			String tmpPath = Constant.SERVERPATH + schoolid + "/" + userName + "/" + name + ".xlsx";
			File file = new File(tmpPath);
			if (!file.exists()) {
				file.getParentFile().mkdirs();// 保证路径存在
			}
			OutputStream outputStream = new FileOutputStream(tmpPath);
			workbook.write(outputStream);
			outputStream.close();
			return Constant.FICTITIOUSPATH + schoolid + "/" + userName + "/" + name + ".xlsx";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "";
	}
	
	public static void main(String[] args) {
		
//		Expression ex = new Expression();//查询条件拼接工具
//		ex.andEqu("schoolid", "YNKMYTXX", String.class);
//		ex.andEqu("deepth", 1, Integer.class);
//		String strWhere = ex.toString();
//		List<TCloudRole> roleList = DAOFactory.getTCloudRoleDAO().getRoleListByStrWhere(strWhere);
//		JSONArray jsonArray = JSONArray.fromObject(roleList);
//		String pathlList = exportExcel2007("角色信息", jsonArray, "Excel导出测试", "321");
//		try {
//			//File file = new File(pathlList.get(0));
//			//file.delete();
//			System.out.println(pathlList);
//			delAllFile("Excel导出测试");
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		
//		Expression ex = new Expression();//查询条件拼接工具
//		ex.andEqu("schoolid", "YNKMYTXX", String.class);
//		ex.andEqu("parentid", Constant.TEACHER, Integer.class);
//		List<TCloudRole> roleList = DAOFactory.getTCloudRoleDAO().getRoleListByStrWhere(ex.toString());
//		JSONArray jsonArray = JSONArray.fromObject(roleList);
//		System.out.println(teacherexportExcel2003("测试", jsonArray, "测试", "测试"));
		
		System.out.println(studentexportExcel2003("测试", "测试", "测试"));
		
	}
	
}
